package com.hq.door.util;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * 作者：袁健宝 日期：2017年1月7日 下午2:11:27
 */
public class ExcelUtil {
	//定义导入教师信息Excel表的表头常量
	public static final String TCODE="教师编号";
	public static final String TNAME="教师姓名";
	public static final String TMOBILE="手机号码";
	public static final String TACCOUNT="账号";
	public static final String TPASSWORD="密码";
	//===================================

	/**
	 * 读取Excel文件
	 * 
	 * @param path
	 *            Excel模板路径
	 * @return 键值对集合
	 */
	public static List<Map<String, String>> readExcel(String path) {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		List<String> keyList = new ArrayList<String>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			File file = new File(path);// 创建文件对象
			FileInputStream fis = new FileInputStream(file);
			Workbook workbook = WorkbookFactory.create(fis);
			int sheetCount = workbook.getNumberOfSheets();// Sheet的数量
			// 遍历Sheet
			for (int i = 0; i < sheetCount; i++) {
				Sheet sheet = workbook.getSheetAt(i);
				int rowCount = sheet.getPhysicalNumberOfRows();// 获取总行数
				// 获取表头行，根据表头获取总列数
				Row row = sheet.getRow(0);
				int cellCount = row.getPhysicalNumberOfCells();// 获取总列数
				for (int k = 0; k < cellCount; k++) {
					Cell cell = row.getCell(k);
					keyList.add(obj2Str(cell));
				}
				// 遍历非表头行，读取内容
				Map<String, String> map = null;
				for (int j = 1; j < rowCount; j++) {
					row = sheet.getRow(j);
					map = new HashMap<String, String>();
					for (int k = 0; k < cellCount; k++) {
						Cell cell = row.getCell(k);
						map.put(keyList.get(k), obj2Str(cell));
					}
					list.add(map);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 字符串转日期
	 * 
	 * @param str
	 * @return
	 */
	public static Date str2Date(String str) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = null;
		try {
			if (str != null && !str.equals("")) {
				System.out.println(str);
				date = sdf.parse(str);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return date;
	}

	/**
	 * Object转字符串
	 * 
	 * @param obj
	 * @return
	 */
	public static String obj2Str(Object obj) {
		String value = "";
		if (obj != null) {
			value = obj.toString();
		}
		return value;
	}

	/**
	 * 字符串转整型
	 * 
	 * @param str
	 * @return
	 */
	public static Integer str2Int(String str) {
		Integer value = null;
		if (str != null && !str.equals("")) {
			value = Integer.parseInt(str.split("\\.")[0]);
		}
		return value;
	}

	/**
	 * 字符串转Double型
	 * 
	 * @param str
	 * @return
	 */
	public static Double str2Double(String str) {
		Double value = null;
		if (str != null && !str.equals("")) {
			value = Double.parseDouble(str);
		}
		return value;
	}

	public static List<Map<String, String>> readExcelOfTeachers(String path) {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		List<String> keyList = new ArrayList<String>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			File file = new File(path);// 创建文件对象
			FileInputStream fis = new FileInputStream(file);
			Workbook workbook = WorkbookFactory.create(fis);
			int sheetCount = workbook.getNumberOfSheets();// Sheet的数量
			// 遍历Sheet
			for (int i = 0; i < sheetCount; i++) {
				Sheet sheet = workbook.getSheetAt(i);
				int rowCount = sheet.getPhysicalNumberOfRows();// 获取总行数
				// 获取表头行，根据表头获取总列数
				Row row = sheet.getRow(0);
				int cellCount = row.getPhysicalNumberOfCells();// 获取总列数
				for (int k = 0; k < cellCount; k++) {
					Cell cell = row.getCell(k);
					keyList.add(obj2Str(cell));
				}
				// 遍历非表头行，读取内容
				Map<String, String> map = null;
				// 将excel中小数格式化成整数类型
				DecimalFormat df = new DecimalFormat("#");
				for (int j = 1; j < rowCount; j++) {
					row = sheet.getRow(j);
					map = new HashMap<String, String>();
					for (int k = 0; k < cellCount; k++) {
						Cell cell = row.getCell(k);
						if(cell==null){
							map.put(keyList.get(k), "");
							continue;
						}
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_NUMERIC:
								String cellToString = df.format(cell
										.getNumericCellValue());
								map.put(keyList.get(k), obj2Str(cellToString));
							
							break;
							
						default:
							map.put(keyList.get(k), obj2Str(cell));
							break;

						}
						
//						System.out.println(cell.getCellType());
//						map.put(keyList.get(k), obj2Str(cell));

					}
					list.add(map);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

}
